/**
 * Copyright (c) 2009, http://www.softwareinlife.com. All rights reserved.
 * 
 * Redistribution and use in source and binary forms, 
 * with or without modification, are permitted provided that 
 * the following conditions are met:
 * 
 * 1. Redistributions of source code must retain the above copyright notice, 
 *    this list of conditions and the following disclaimer.
 * 
 * 2. Redistributions in binary form must reproduce the above copyright notice, 
 *    this list of conditions and the following disclaimer in the documentation 
 *    and/or other materials provided with the distribution.
 * 
 * 3. Neither the name of the http://www.softwareinlife.com nor the names of its 
 *    contributors may be used to endorse or promote products derived from 
 *    this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE SOFTWAREINLIFE AND CONTRIBUTORS "AS IS" 
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. 
 * IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED 
 * AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package com.softwareinlife.vision.android.data.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.softwareinlife.vision.android.data.exceptions.DatabaseConnectionException;
import com.softwareinlife.vision.android.data.exceptions.UserCreateException;
import com.softwareinlife.vision.android.data.exceptions.UserReadException;
import com.softwareinlife.vision.android.data.exceptions.UserUpdateException;
import com.softwareinlife.vision.android.data.exceptions.VisionCreateException;
import com.softwareinlife.vision.android.data.model.UserModel;
import com.softwareinlife.vision.android.system.util.StringUtil;

/**
 * @author Jang, Sun-Jin (jangsunjin@softwareinlife.com)
 * @date 2009. 10. 11.
 */
public class UserDAO extends BaseDAO {

	/**
	 * Constructor 
	 * 
	 * @param ctx
	 * @throws DatabaseConnectionException
	 */
	public UserDAO(Context ctx) throws DatabaseConnectionException {
		super(ctx);
	}

	/**
	 * Constructor
	 * 
	 * @param db
	 */
	public UserDAO(SQLiteDatabase db) {
		super(db);
	}

	/**
	 * Create Table
	 * 
	 * @param model
	 * @throws VisionCreateException
	 */
	public void createTable() throws UserCreateException {
		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE IF NOT EXISTS ");
		sql.append(UserConstants.TABLE_NAME + " ");
		sql.append("(");
		sql.append(UserConstants.COLUMN_OF_IDS
				+ " VARCHAR(40) PRIMARY KEY, ");
		sql.append(UserConstants.COLUMN_OF_PASSWORD
				+ " VARCHAR(40) NOT NULL, ");
		sql.append(UserConstants.COLUMN_OF_FIRST_NAME + " VARCHAR(40), ");
		sql.append(UserConstants.COLUMN_OF_LAST_NAME + " VARCHAR(40), ");
		sql.append(UserConstants.COLUMN_OF_NICK_NAME + " VARCHAR(40), ");
		sql.append(UserConstants.COLUMN_OF_EMAIL + " VARCHAR(255), ");
		sql.append(UserConstants.COLUMN_OF_CREATED_DATE
				+ " VARCHAR(50) NOT NULL, ");
		sql.append(UserConstants.COLUMN_OF_MODIFIED_DATE
				+ " VARCHAR(50) NOT NULL, ");
		sql.append(UserConstants.COLUMN_OF_SYNC_DATE + " VARCHAR(50)");
		sql.append(");");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Create User
	 * 
	 * @param model
	 * @throws UserCreateException
	 */
	public void create(UserModel model) throws UserCreateException {
		
		// Parameter Check
		if (model != null
				&& (model.getUserId() != null && model.getUserId().length() > 0)
				&& (model.getUserPassword() != null && model.getUserPassword()
						.length() > 0) && (model.getCreatedDate() != null)
				&& (model.getModifiedDate() != null)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO ");
			sql.append(UserConstants.TABLE_NAME + " ");
			sql.append("(");
			sql.append(UserConstants.COLUMN_OF_IDS + ", ");
			sql.append(UserConstants.COLUMN_OF_PASSWORD + ", ");
			sql.append(UserConstants.COLUMN_OF_FIRST_NAME + ", ");
			sql.append(UserConstants.COLUMN_OF_LAST_NAME + ", ");
			sql.append(UserConstants.COLUMN_OF_NICK_NAME + ", ");
			sql.append(UserConstants.COLUMN_OF_EMAIL + ", ");
			sql.append(UserConstants.COLUMN_OF_CREATED_DATE + ", ");
			sql.append(UserConstants.COLUMN_OF_MODIFIED_DATE);
			sql.append(") ");
			sql.append("VALUES ");
			sql.append("(");
			sql
					.append("'" + StringUtil.toString(model.getUserId()) + "'"
							+ ", ");
			sql.append("'" + StringUtil.toString(model.getUserPassword()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getFirstName()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getLastName()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getNickName()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getEMail()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getCreatedDate()) + "'"
					+ ", ");
			sql
					.append("'" + StringUtil.toString(model.getModifiedDate())
							+ "'");
			sql.append(");");

			System.out.println(sql.toString());

			db.execSQL(sql.toString());
		}
	}

	/**
	 * Update User
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public void update(UserModel model) throws UserUpdateException {

		// Parameter Check
		if (model != null
				&& (model.getUserId() != null && model.getUserId().length() > 0)
				&& (model.getUserPassword() != null && model.getUserPassword()
						.length() > 0) && (model.getCreatedDate() != null)
				&& (model.getModifiedDate() != null)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("UPDATE ");
			sql.append(UserConstants.TABLE_NAME + " ");
			sql.append("SET ");
			sql.append(UserConstants.COLUMN_OF_IDS + " = " + "'"
					+ StringUtil.toString(model.getUserId()) + "'" + " , ");
			sql.append(UserConstants.COLUMN_OF_PASSWORD + " = " + "'"
					+ StringUtil.toString(model.getUserPassword()) + "'" + " , ");
			sql.append(UserConstants.COLUMN_OF_FIRST_NAME + " = " + "'"
					+ StringUtil.toString(model.getFirstName()) + "'" + ", ");
			sql.append(UserConstants.COLUMN_OF_LAST_NAME + " = " + "'"
					+ StringUtil.toString(model.getLastName()) + "'" + ", ");
			sql.append(UserConstants.COLUMN_OF_NICK_NAME + " = " + "'"
					+ StringUtil.toString(model.getNickName()) + "'" + ", ");
			sql.append(UserConstants.COLUMN_OF_EMAIL + " = " + "'"
					+ StringUtil.toString(model.getEMail()) + "'" + ", ");
			sql.append(UserConstants.COLUMN_OF_MODIFIED_DATE + " = " + "'"
					+ StringUtil.toString(model.getModifiedDate()) + "'");
			sql.append(" ");
			sql.append("WHERE ");
			sql.append(UserConstants.COLUMN_OF_IDS + " = " + "'"
					+ model.getIds() + "'");

			System.out.println(sql.toString());
			db.execSQL(sql.toString());
		}
	}

	/**
	 * Update User
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public void updateSyncDate(String ids) throws UserUpdateException {

		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(UserConstants.TABLE_NAME + " ");
		sql.append("SET ");
		sql.append(UserConstants.COLUMN_OF_SYNC_DATE + " = " + "'"
				+ StringUtil.toString(new Date()) + "'");
		sql.append(" ");
		sql.append("WHERE ");
		sql.append(UserConstants.COLUMN_OF_IDS + " = " + "'" + ids + "'");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Delete User
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public void delete(UserModel model) throws UserUpdateException {

		// Parameter Check
		if (model != null
				&& (model.getUserId() != null && model.getUserId().length() > 0)
				&& (model.getUserPassword() != null && model.getUserPassword()
						.length() > 0) && (model.getCreatedDate() != null)
				&& (model.getModifiedDate() != null)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("DELETE FROM ");
			sql.append(UserConstants.TABLE_NAME + " ");
			sql.append("WHERE ");
			sql.append(UserConstants.COLUMN_OF_IDS + " = " + "'"
					+ model.getUserId() + "'");

			System.out.println(sql.toString());

			db.execSQL(sql.toString());
		}
	}

	/**
	 * Delete User
	 * 
	 * delete from VS_User where IDS = 1;
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public void delete(String ids) throws UserUpdateException {

		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(UserConstants.TABLE_NAME + " ");
		sql.append("WHERE ");
		sql.append(UserConstants.COLUMN_OF_IDS + " = " + "'" + ids + "'");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Read User
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public List<UserModel> readAll() throws UserReadException {

		// Results
		List<UserModel> results = new ArrayList<UserModel>(0);

		// Columns
		String[] columns = new String[] { UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_FIRST_NAME,
				UserConstants.COLUMN_OF_LAST_NAME,
				UserConstants.COLUMN_OF_NICK_NAME,
				UserConstants.COLUMN_OF_EMAIL,
				UserConstants.COLUMN_OF_CREATED_DATE,
				UserConstants.COLUMN_OF_MODIFIED_DATE,
				UserConstants.COLUMN_OF_SYNC_DATE };

		// Query
		Cursor cursor = db.query(UserConstants.TABLE_NAME, columns, null, null,
				null, null, UserConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					UserModel model = new UserModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);
						
						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}

	public UserModel read(String UserIds) {
		// Results
		UserModel model = null;

		// Columns
		String[] columns = new String[] { UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_FIRST_NAME,
				UserConstants.COLUMN_OF_LAST_NAME,
				UserConstants.COLUMN_OF_NICK_NAME,
				UserConstants.COLUMN_OF_EMAIL,
				UserConstants.COLUMN_OF_CREATED_DATE,
				UserConstants.COLUMN_OF_MODIFIED_DATE,
				UserConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = UserConstants.COLUMN_OF_IDS + " = '" + UserIds + "' ";

		// Query
		Cursor cursor = db.query(UserConstants.TABLE_NAME, columns, selection,
				null, null, null, UserConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				model = new UserModel();
				for (int i = 0; i < cursor.getColumnCount(); i++) {
					String columnName = cursor.getColumnName(i);
					String columnValue = cursor.getString(i);

					model = putDate(model, columnName, columnValue);
				}
			}
		}

		return model;
	}

	/**
	 * Read User Sync Data
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public List<UserModel> readSyncData() throws UserReadException {

		// Results
		List<UserModel> results = new ArrayList<UserModel>(0);

		// Add Is Null Results
		List<UserModel> isNull = readSyncDataIsNull();
		results.addAll(isNull);

		// Add Is Chanaged Results
		List<UserModel> isChanged = readSyncDataIsNull();
		results.addAll(isChanged);

		return results;
	}

	/**
	 * Read User Sync Data is Null
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public List<UserModel> readSyncDataIsNull() throws UserReadException {

		// Results
		List<UserModel> results = new ArrayList<UserModel>(0);

		// Columns
		String[] columns = new String[] { UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_FIRST_NAME,
				UserConstants.COLUMN_OF_LAST_NAME,
				UserConstants.COLUMN_OF_NICK_NAME,
				UserConstants.COLUMN_OF_EMAIL,
				UserConstants.COLUMN_OF_CREATED_DATE,
				UserConstants.COLUMN_OF_MODIFIED_DATE,
				UserConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = "SYNC_DATE is null";

		// Query
		Cursor cursor = db.query(UserConstants.TABLE_NAME, columns, selection,
				null, null, null, UserConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					UserModel model = new UserModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);

						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}

	/**
	 * Read User Sync Data is Null and Not Syncronized
	 * 
	 * select * from VS_User where SYNC_DATE is not null and
	 * julianday(MODIFIED_DATE) > julianday(SYNC_DATE);
	 * 
	 * @param model
	 * @throws UserUpdateException
	 */
	public List<UserModel> readSyncDataIsChanged() throws UserReadException {

		// Results
		List<UserModel> results = new ArrayList<UserModel>(0);

		// Columns
		String[] columns = new String[] { UserConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_FIRST_NAME,
				UserConstants.COLUMN_OF_LAST_NAME,
				UserConstants.COLUMN_OF_NICK_NAME,
				UserConstants.COLUMN_OF_EMAIL,
				UserConstants.COLUMN_OF_CREATED_DATE,
				UserConstants.COLUMN_OF_MODIFIED_DATE,
				UserConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = "SYNC_DATE is not null and julianday(MODIFIED_DATE) > julianday(SYNC_DATE)";

		// Query
		Cursor cursor = db.query(UserConstants.TABLE_NAME, columns, selection,
				null, null, null, UserConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					UserModel model = new UserModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);

						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}
	
	/**
	 * Put Data
	 * 
	 * @param model
	 * @param columnName
	 * @param columnValue
	 */
	public UserModel putDate(UserModel model, String columnName, String columnValue) {
		if (UserConstants.COLUMN_OF_IDS.equals(columnName)) {
			model.setUserId(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_PASSWORD.equals(columnName)) {
			model.setUserPassword(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_FIRST_NAME.equals(columnName)) {
			model.setFirstName(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_LAST_NAME.equals(columnName)) {
			model.setLastName(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_NICK_NAME.equals(columnName)) {
			model.setNickName(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_EMAIL.equals(columnName)) {
			model.setEMail(StringUtil.toString(columnValue));
		} else if (UserConstants.COLUMN_OF_CREATED_DATE.equals(columnName)) {
			model.setCreatedDate(StringUtil.toDate(columnValue));
		} else if (UserConstants.COLUMN_OF_MODIFIED_DATE.equals(columnName)) {
			model.setModifiedDate(StringUtil.toDate(columnValue));
		} else if (UserConstants.COLUMN_OF_SYNC_DATE.equals(columnName)) {
			model.setSyncDate(StringUtil.toDate(columnValue));
		}
		
		return model;
	}
}
